﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Drawing;

using zencat.sqlite;

namespace CatDb
{
    class CatDb {

        static CatDb() {
            Sqlite.Initialize();

            // uncomment to trace to trace file (will work with unicode)
            Sqlite.TraceDestination = TrcDestination.TraceToFile;
            Sqlite.TraceFileName = 
                Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "CatDb.log");

            // uncomment to trace to stdout - will show up in VC# console. Kind of slow though.
            // Sqlite.TraceDestination = TrcDestination.TraceToStdout;

            Sqlite.TraceLevel = TrcLevel.Verbose;
        }

        IDatabase _database;
        
        IStatement _stmt_insert;
        IStatement _stmt_update;
        IStatement _stmt_delete;
        IStatement _stmt_loadAll;

        /// <summary>
        /// creates a new cat database or opens an existing one.
        /// </summary>
        /// <param name="file"></param>
        public void open(string file) {
            bool willCreate = ! File.Exists(file);
            _database = Sqlite.OpenDatabase(file);
            Debug.Assert(File.Exists(file));
            if (willCreate) {
                createSchema();
            }
            createPreparedStatements();
            if (willCreate) {
                createSomeCats();
            }
        }

        /// <summary>
        /// close cat database
        /// </summary>
        public void close() {
            _database.Close();
        }

        /// <summary>
        /// creates schema for cat db
        /// </summary>
        private void createSchema() {

            // store strings as unicode
            _database.ExecuteSql("PRAGMA encoding=\"UTF-16\";");

            _database.ExecuteSql("CREATE TABLE CAT " +
                "( " +
				"id INTEGER PRIMARY KEY "       + "," +
                "age INTEGER "          + "," +
                "gender TEXT "          + "," +
                "description TEXT "     + "," +
                "name TEXT "            + "," +
				"neutered INTEGER "     +
				" ); ");

         }

        private void createSomeCats() {
            saveCat(new Cat(-1, 5, Gender.female, true, "Lili", "tricolor-spotted"));
            saveCat(new Cat(-1, 12, Gender.female, false, "Lucy", "black"));
            saveCat(new Cat(-1, 5, Gender.male, true, "Arnie", "white-gray"));
            saveCat(new Cat(-1, 2, Gender.female, true, "кошка", "Черный, с белыми пятнами"));
            saveCat(new Cat(-1, 2, Gender.female, true, "ねこ", "赤のストライプ"));
        
        }

        /// <summary>
        /// initialize prepared statements
        /// </summary>
        private void createPreparedStatements() {

            Debug.Assert(_database != null);
            
            _stmt_loadAll = _database.CreateStatement(
                "SELECT id, age, gender, description, name, neutered FROM CAT ORDER BY id"
                );

            _stmt_insert = _database.CreateStatement(
                "INSERT INTO CAT (age, gender, description, name, neutered) VALUES(?,?,?,?,?)"
                );

            _stmt_update = _database.CreateStatement(
                "UPDATE CAT SET age=?, gender=?, description=?, name=?, neutered=? WHERE id=?"
                );

            _stmt_delete = _database.CreateStatement(
                "DELETE FROM CAT WHERE id=?"
                );
        
        }

        private Cat catFromRow(string[] values) {

            Cat cat = new Cat();

            Debug.Assert(values.Length == 6);

            int i = 0;

            cat.Id = Int64.Parse(values[i]);
            i++;
            cat.Age = Int32.Parse(values[i]);
            i++;
            cat.Description = values[i];
            i++;
            cat.Gender = (Gender) Enum.Parse(typeof(Gender), values[i]);
            i++;
            cat.Name = values[i];
            i++;
            cat.Neutered = Boolean.Parse(values[i]);
            i++;

            return cat;

        }

        private string[] rowFromCat(Cat cat, bool withId) {

            string[] values = new string[withId ? 6 : 5];

            int i = 0;

            values[i] = cat.Age.ToString();
            i++;
            values[i] = cat.Description.ToString();
            i++;
            values[i] = cat.Gender.ToString();
            i++;
            values[i] = cat.Name;
            i++;
            values[i] = cat.Neutered.ToString();
            i++;

            if (withId) {
                values[i] = cat.Id.ToString();
                i++;
            }

            return values;
        
        }


        /// <summary>
        /// retrieve all cats from the database
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Cat> getAllCats() {

            List<Cat> output = new List<Cat>();

            _stmt_loadAll.Reset();

            while (_stmt_loadAll.Step()) {
                string[] row = _stmt_loadAll.ColumnValues();
                Cat cat = catFromRow(row);
                output.Add(cat);
            }

            return output;
        
        }

        /// <summary>
        /// insert a new cat in the db. Cat.Id will contain the db key.
        /// </summary>
        /// <param name="cat"></param>
        private void insertCat(Cat cat) {

            Debug.Assert(cat.Id == -1);

            // "INSERT INTO CAT (age, gender, color, name, neutered) VALUES(?,?,?,?,?)"
            string[] values = rowFromCat(cat, false);
            Debug.Assert(values.Length == 5);

            _stmt_insert.Reset();
            
            _stmt_insert.BindParameters(values);

            _stmt_insert.Step();

            Debug.Assert(!_stmt_update.HasData);
            Debug.Assert(_database.NumberOfChangedRows == 1);

            cat.Id = (int) _database.LastInsertRowId;
            
        }

        private void updateCat(Cat cat) {

            Debug.Assert(cat.Id != -1);

            //  "UPDATE CAT SET age=?, gender=?, color=?, name=?, neutered=?) WHERE id=?"
            string[] values = rowFromCat(cat, true);
            Debug.Assert(values.Length == 6);

            _stmt_update.Reset();

            _stmt_update.BindParameters(values);
            
            _stmt_update.Step();

            Debug.Assert(!_stmt_update.HasData);
            Debug.Assert(_database.NumberOfChangedRows == 1);

        }

        public void saveCat(Cat cat) {

            if (cat.Id == -1) {
                insertCat(cat);
            } else {
                updateCat(cat);
            }
        
        }

        public void deleteCat(Cat cat) {
            Debug.Assert(cat.Id != -1);
            _stmt_delete.Reset();
            _stmt_delete.BindInt64ToParameter(cat.Id, 1);
            _stmt_delete.Step();
            Debug.Assert(!_stmt_delete.HasData);
            Debug.Assert(_database.NumberOfChangedRows == 1);
        }


    }
}
